package mya.mes.mdc.logic.tag2;


import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelParse {

    public static String EXCEL_FILE_NAME = "D:\\mya\\project210\\mes-mdc-parent\\mes-mdc-module\\src\\main\\resources\\TAG_WCS.xlsx";
    boolean processFormula = false;

    DataFormatter formatter = new DataFormatter();

    FormulaEvaluator evaluator;

    public static void main(String[] args) {
        // Use a file
        try {

            ExcelParse parse = new ExcelParse();

            ExcelData data = parse.parse(EXCEL_FILE_NAME);

            System.out.println(data.tagDataDefList.size() + " ," + data.tagAlarmDefList.size());

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public ExcelData parse(String excelFile) throws IOException {
        ExcelData excelData = new ExcelData();

        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(excelFile));

            evaluator = wb.getCreationHelper().createFormulaEvaluator();

            int sheetCount = wb.getNumberOfSheets();

            Sheet sheet0 = wb.getSheetAt(0);
            Sheet sheet1 = wb.getSheetAt(1);

            if (sheetCount == 2) {


                readSheet0(sheet0, excelData.tagDataDefList);
                excelData.tagStatusDefList = null;
                readSheet1(sheet1, excelData.tagAlarmDefList);
            } else if (sheetCount > 2) {

                excelData.tagStatusDefList = new ArrayList<>(1024);
                Sheet sheet2 = wb.getSheetAt(2);

                readSheet0(sheet0, excelData.tagDataDefList);
                readSheet1(sheet1, excelData.tagStatusDefList);
                readSheet1(sheet2, excelData.tagAlarmDefList);
            }


            wb.close();
            wb = null;

        } finally {
            if (wb != null) {
                wb.close();
            }
        }
        return excelData;
    }

    private void readSheet0(Sheet sheet, List<ExcelTagDataDef> list) throws IOException {


        for (Row row : sheet) {
            if (row.getRowNum() < 1) {
                //忽略表格的标题行
                continue;
            }
            ExcelTagDataDef tagDef = new ExcelTagDataDef();

            tagDef.rowNum = row.getRowNum();

            tagDef.tagName = getString(row, 0);
            tagDef.plcAddress = getString(row, 1);
            tagDef.dataType = getString(row, 2);
            tagDef.respectDataType = getString(row, 3);
            tagDef.clientAddress = getString(row, 4);
            tagDef.scanRate = getString(row, 5);
            tagDef.scaling = getString(row, 6);

            tagDef.rawLow = getString(row, 7);
            tagDef.rawHigh = getString(row, 8);

            tagDef.scaledLow = getString(row, 9);
            tagDef.scaledHigh = getString(row, 10);

            tagDef.scaledDataType = getString(row, 11);

            tagDef.clampLow = getString(row, 12);
            tagDef.clampHigh = getString(row, 13);

            tagDef.engUnits = getString(row, 14);
            tagDef.description = getString(row, 15);

            tagDef.negateValue = getString(row, 16);


            list.add(tagDef);
        }
    }

    private void readSheet1(Sheet sheet, List<ExcelTagAlarmDef> list) throws IOException {
        for (Row row : sheet) {

            if (row.getRowNum() < 1) {
                //忽略表格的标题行
                continue;
            }

            ExcelTagAlarmDef alertDef = new ExcelTagAlarmDef();

            alertDef.rowNum = row.getRowNum();

            alertDef.plcAddress = getString(row, 0);
            alertDef.no = getString(row, 1);
            alertDef.index = getInteger(row, 2);
            alertDef.bit = getInteger(row, 3);
            alertDef.rdbTag = getString(row, 4);
            alertDef.ref = getString(row, 5);
            alertDef.enUs = getString(row, 6);
            alertDef.zhCN = getString(row, 7);
            alertDef.msgNotice = getString(row, 8) != null ? false : true;

            if ("0".equals(alertDef.rdbTag)) {
                continue;
            }
//      if(isEmpty(alertDef.plcAddress) && isEmpty(alertDef.rdbTag)) {
//        continue;
//      }
            list.add(alertDef);
        }
    }

    private boolean isEmpty(String str) {
        if (str == null || str.isEmpty()) {
            return true;
        }

        str = str.trim();

        return str.isEmpty();
    }

    private String getString(Row row, int cellIndex) {

        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return null;
        }

        CellType cellType = cell.getCellType();

        if (CellType.BLANK.equals(cellType)) {
            return null;
        }


        if (CellType.FORMULA.equals(cellType)) {

            if (!processFormula) {
                return null;
            }
            CellValue cellValue = evaluator.evaluate(cell);

            CellType cellType1 = cellValue.getCellType();


            if (CellType.STRING.equals(cellType1)) {
                return cellValue.getStringValue();
            } else if (CellType.ERROR.equals(cellType1)) {
                return null;
            } else if (CellType.BLANK.equals(cellType1)) {
                return null;
            } else {
                return cellValue.formatAsString();
            }

        }


        String text = formatter.formatCellValue(cell);

        if (text != null) {
            text = text.trim();
        }
        return text;
    }

    private Integer getInteger(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return null;
        }

        CellType cellType = cell.getCellType();

        if (CellType.BLANK.equals(cellType) || CellType.ERROR.equals(cellType)) {
            return null;
        }


        if (CellType.FORMULA.equals(cellType)) {
            if (!processFormula) {
                return null;
            }
            CellValue cellValue = evaluator.evaluate(cell);

            CellType cellType1 = cellValue.getCellType();

            if (CellType.BLANK.equals(cellType1) || CellType.ERROR.equals(cellType1)) {
                return null;
            }

            if (CellType.NUMERIC.equals(cellType1)) {
                return new Double(cellValue.getNumberValue()).intValue();
            }


            if (CellType.STRING.equals(cellType1)) {
                String text = cellValue.getStringValue();

                return getAsInt(text);
            }

            return null;

//      if (CellType.NUMERIC.equals(cellType1)) {
//        return cellValue.getStringValue();
//      } else if (CellType.ERROR.equals(cellType1)) {
//        return null;
//      } else if (CellType.BLANK.equals(cellType1)) {
//        return null;
//      } else {
//        return cellValue.formatAsString();
//      }

        }


        if (CellType.NUMERIC.equals(cellType)) {

            return new Double(cell.getNumericCellValue()).intValue();
        } else if (CellType.STRING.equals(cellType)) {

            String text = formatter.formatCellValue(cell);
            return getAsInt(text);

        } else if (CellType.FORMULA.equals(cellType)) {
            cell.getCellFormula();
        }

        return null;
    }

    private Integer getAsInt(String text) {
        if (text != null) {
            text = text.trim();
        }

        if (text == null || text.isEmpty()) {
            return null;
        }

        try {
            return Integer.parseInt(text);
        } catch (Exception e) {
            return null;
        }

    }
}
